package com.xyhy.core.file.utils;

import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * @author Fire Team - Robert
 * @Description POI工具类
 * @date 2021年9月20日
 */
public class POIUtil {
  /**
   * 获取 cell 的 value
   *
   * @param cell
   * @return
   */
  public static String getCellValue(Cell cell) {
    if (cell == null) {
      return null;
    }
    CellType cellType = cell.getCellType();
    Object value = null;
    switch (cellType) {
      case BLANK:
        value = "";
        break;
      case BOOLEAN:
        value = cell.getBooleanCellValue();
        break;
      case ERROR:
        value = cell.getErrorCellValue();
        break;
      case FORMULA:
        value = getForMulaValue(cell);
        break;
      case NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
          SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
          Date date = DateUtil.getJavaDate(cell.getNumericCellValue());
          value = sdf.format(date);
        } else {
          double doubleVal = cell.getNumericCellValue();
          long longVal = new Double(doubleVal).longValue();
          if (doubleVal == longVal) {
            value = longVal + "";
          } else {
            BigDecimal bd = new BigDecimal(doubleVal + "");
            value = bd.toPlainString();
          }
        }

        break;
      case STRING:
        value = cell.getStringCellValue();
        break;
      default:
        break;
    }
    return value.toString();
  }

  private static Object getForMulaValue(Cell cell) {
    Workbook wb = cell.getSheet().getWorkbook();
    HSSFFormulaEvaluator hssfEvaluator = null;
    XSSFFormulaEvaluator xssfEvaluator = null;
    CellValue cellValue = null;

    if (wb instanceof HSSFWorkbook) {
      hssfEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) wb);
      cellValue = hssfEvaluator.evaluate(cell);
    } else if (wb instanceof XSSFWorkbook) {
      xssfEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) wb);
      cellValue = xssfEvaluator.evaluate(cell);
    }

    CellType cellValueType = cellValue.getCellType();
    switch (cellValueType) {
      case BLANK:
        return "";
      case BOOLEAN:
        return cellValue.getBooleanValue();
      case ERROR:
        return cellValue.getErrorValue();
      case NUMERIC:
        double str = cellValue.getNumberValue();
        DecimalFormat df = new DecimalFormat("0.000000");
        return df.format(str);
      case STRING:
        return cellValue.getStringValue();
      default:
        return null;
    }
  }

  public static XSSFCell createCell(XSSFRow row, int colIdx) {
    return row.createCell(colIdx);
  }

  public static XSSFCell createWrapCell(XSSFRow row, int colIdx) {
    XSSFCellStyle cellStyle = row.getSheet().getWorkbook().createCellStyle();
    cellStyle.setWrapText(true);
    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    cellStyle.setAlignment(HorizontalAlignment.LEFT);

    XSSFCell cell = row.createCell(colIdx);
    cell.setCellStyle(cellStyle);

    return cell;
  }

  public static short parsePx2ExcelRowHeight(int px) {
    return (short) (px * 15);
  }

}
